/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.logging.Level;
import java.util.logging.Logger;

import java.util.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.sql.Time;
import java.util.*;
import java.util.ArrayList;
import org.apache.http.examples.client.BetProperties;
import org.apache.http.examples.client.Debug;
import org.apache.http.examples.client.TimeUtil;

/**
 *
 * @author Vccorp
 */
public class MySQLDataManager {

    private Connection conn = null;
    private String TABLE_BETTING = "betting";
    private String TABLE_VIDEO   = "video";
    private String TABLE_RESULT  = "result";
    private String TABLE_RATE    = "rate";
    private String TABLE_USER    = "user";

    public MySQLDataManager()
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            //conn = DriverManager.getConnection(GFSServer.mysqlConnectionString, GFSServer.mysqlUsername, GFSServer.mysqlPassword);
            conn = DriverManager.getConnection(ACCOUNT_DB.URL , ACCOUNT_DB.username, ACCOUNT_DB.pass);//abc!@#
            // conn = DriverManager.getConnection("jdbc:mysql://192.168.5.89:3306/markstore" , "markstore", "CMKQcFd5L6zWJ3xM");//abc!@#
            //conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/markstore" , "root", "hegemonie");//abc!@#
            Debug.println("Connected to MySQL GFS Server database");
        }
        catch (Exception ex)
        {
            System.out.println("Couldn't connect to the MySQL database.  Make sure the MySQL connector jar is in the lib directory.  See the installation documentation for more information. ");
            System.out.println(ex.getMessage());
            Debug.println(ex.getMessage());
        }
    }
    
    public boolean insertResultInDB(String team1,String team2,String euro_1,
                                    String euro_2,String euro_3,String asia_1,
                                    String asia_2,String asia_3,String tx_1,String tx_2,
                                    String tx_3,String hour,String day,String group) throws ParseException
    {
        boolean check = false;
        String date = day + "/" + String.valueOf(TimeUtil.getYear());
        String[] arr_day = new String [100];
        arr_day = day.split("/");
        //handle date to get start_time type unixtime
        long start_time = TimeUtil.convertDatetoUnixTime(hour, arr_day[0], arr_day[1]);       
        try {            
            Statement s = conn.createStatement();
            String sql = "insert into "+ TABLE_RATE +" values (DEFAULT, '" + team1 + "', '" + team2 +"', '" + euro_1 +"', '" + euro_2 +"', '" + euro_3 +"', '" + asia_1 +"', '" + asia_2 +"', '" + asia_3 +"', '" + tx_1 +"', '" + tx_2 +"', '" + tx_3 +"', '" + hour +"', '" + date +"','" + start_time +"', '" + group +"', '" + TimeUtil.getCurrentTime() +"')";
            //Debug.println(sql);
            s.execute(sql);
            check = true;
            s.close();
                           
        } catch (Exception ex)
        {
          System.out.println(ex.getMessage());  
        }
                        
        return check;
    }
    /*
     * Check double when insert rate
     * author quyennb
     */
    public boolean checkExistDoubleAsianAndEuroRate(String team1,String team2,String hour,String day) throws ParseException{
        boolean retVal = false;        
        String date = day + "/" + String.valueOf(TimeUtil.getYear());
        String[] arr_day = new String [100];
        arr_day = day.split("/");
        //handle date to get start_time type unixtime
        long maxTime = TimeUtil.convertDatetoUnixTime(hour, arr_day[0], arr_day[1]);        
        long minTime = maxTime - 24*60*60;
        
        try
        {
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery("select * from "+ TABLE_RATE +" where team1 = '" + team1 + "' AND team2 = '" + team2 + "'AND start_time > " + minTime + " AND start_time <= " + maxTime);
            if (rs.next())
            {
                retVal = true;
            }
            s.close();
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }

        return retVal;
    }

    public boolean checkExistAsianAndEuroRate(String team1,String team2,String hour,String day){
        boolean retVal = false;
        String date = day + "/" + String.valueOf(TimeUtil.getYear());
        try
        {
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery("select * from "+ TABLE_RATE +" where team1 = '" + team1 + "' AND team2 = '" + team2 + "'AND hour = '" + hour + "' AND day = '" + date + "'");
            if (rs.next())
            {
                retVal = true;
            }
            s.close();
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }

        return retVal;
    }

    public boolean updateTX(String team1,String team2,String tx_1,String tx_2,String tx_3)
    {
         boolean check = false;
         try {
            Statement s = conn.createStatement();
            String sql = "UPDATE "+ TABLE_RATE + " SET  tx_1 = '" + tx_1 + "'," + " tx_2 = '" + tx_2 + "'," +" tx_rate = '" + tx_3 + "' WHERE team1 = '"+ team1 +"' AND team2 = '"+ team2 +"'";
            s.execute(sql);
            check = true;
            s.close();

        } catch (Exception ex)
        {
          System.out.println(ex.getMessage());
        }
        return check;

    }

    public boolean updateAsianAndEuroRate(String team1,String team2,String euro_1,
                                    String euro_2,String euro_3,String asia_1,
                                    String asia_2,String asia_3,String tx_1,String tx_2,
                                    String tx_3,String hour,String day){
        boolean check = false;
        String date = day + "/" + String.valueOf(TimeUtil.getYear());
        
         try {
            Statement s = conn.createStatement();
            String sql = "UPDATE "+ TABLE_RATE + " SET  euro_1 = '" + euro_1 + "'," + " euro_rate = '" + euro_2 + "'," +" euro_2 = '" + euro_3 + "'," + " asia_1 = '" + asia_1 + "'," + " asia_rate = '" + asia_2 + "'," +" asia_2 = '" + asia_3 + "' WHERE team1 = '"+ team1 +"' AND team2 = '"+ team2 +"' AND hour = '"+ hour +"'AND day = '"+ date +"'";
            s.execute(sql);
            check = true;
            s.close();

        } catch (Exception ex)
        {
          System.out.println(ex.getMessage());
        }
        return check;
        
    }

    public static String getDateTime() {
        DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        Date date = new Date();
        return dateFormat.format(date);
    }

    public boolean insertResultFoolBall (String team1,String team2,String score,String team_win,String status,String id_tour,String tour_nam,long start_time)
    {
        boolean check = false;
        if(!checkExistResultFB(team1, team2, start_time))
        {
            
             try {
                Statement s = conn.createStatement();
                String sql = "insert into "+ TABLE_RESULT +" values (DEFAULT, '" + team1 + "', '" + team2 +"', '" + score +"', '" + team_win +"', '" + status +"','" + id_tour +"', '"+ tour_nam +"','"+ start_time +"', '" + getDateTime() +"')";
                //Debug.println(sql);
                s.execute(sql);
                check = true;
                s.close();

            } catch (Exception ex)
            {
              System.out.println(ex.getMessage());
            }
             
        }
        return check;
    }

    public boolean checkExistResultFB(String team1,String team2,long start_time)
    {
        boolean retVal = false;
        try
        {
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery("select * from "+ TABLE_RESULT +" where teamname1 = '" + team1 + "' AND teamname2 = '" + team2 + "'AND start_time = '" + start_time + "'");
            if (rs.next())
            {
                retVal = true;
            }
            s.close();
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }

        return retVal;
    }

    public List getResultFB(String team1,String team2,long start_time)
    {
        List Infor = new ArrayList();
        try
        {
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery("select id, score, team_win from "+ TABLE_RESULT +" where teamname1 = '" + team1 + "' AND teamname2 = '" + team2 + "'AND start_time = '" + start_time + "'");
            if (rs.next())
            {                
                Infor.add(rs.getInt("id"));
                Infor.add(rs.getString("score"));
                Infor.add(rs.getString("team_win"));
            }
            s.close();
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }

        return Infor;
    }

    public Set getBetByOpenTime(long open_date)
    {
        Set Infor = new HashSet();
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select id,user_id, category_id, team_selected, rate_add, rate_selected, vs, price, id_rate from "+ TABLE_BETTING +" where open_date = '"+ open_date +"' and profit = 0 and is_increase = 0");

            while(rs.next())
            {
                BetProperties betproperty = new BetProperties();
                betproperty.id = rs.getInt("id");
                betproperty.category_id = rs.getInt("category_id");
                betproperty.user_id = rs.getInt("user_id");
                betproperty.team_selected = rs.getString("team_selected");
                betproperty.team_selected = rs.getString("rate_add");
                betproperty.rate_selected = rs.getFloat("rate_selected");
                betproperty.vs = rs.getString("vs");
                betproperty.price = rs.getInt("price");
                betproperty.id_rate = rs.getString("id_rate");               
                Infor.add(betproperty);
            }
        }
        catch (Exception ex) {
               Debug.println(ex.getMessage());
        }
        return Infor;        
    }

    public boolean updateCoinToTableBet(int bet_id, int profit ,int is_increase)
    {
        boolean check = false;
        try {
            Statement s = conn.createStatement();
            String sql = "UPDATE "+ TABLE_BETTING + " SET  profit = '" + profit + "'," + " is_increase = '" + is_increase + " WHERE id = '"+ bet_id +"'";
            s.execute(sql);
            check = true;
            s.close();

        } catch (Exception ex)
        {
          System.out.println(ex.getMessage());
        }
        return check;
    }

    public boolean updateCoinToTableUser(int user_id, int profit ,int is_increase)
    {    
        boolean check = false;
         
         try {
            Statement s = conn.createStatement();

            String str1 = "coin = if((coin + "+ profit+ " ) > 0,coin + "+ profit +"  ,0)";
            String sql = "update  "+ TABLE_USER +" set "+ str1 +" where id = "+ user_id;            
            s.execute(sql);
            check = true;
            s.close();

        } catch (Exception ex)
        {
          System.out.println(ex.getMessage());
        }

        
        return check;
    }
    //update coin
    public Map getData()
    {
        Map<Integer, String> mapData = new HashMap<Integer, String>();
        try {
            Statement st = conn.createStatement();
         
            String sql = "SELECT dat,u FROM "
                            + "(SELECT DATE(FROM_UNIXTIME(dt)) AS dat ,uid AS u "
                                + "FROM (SELECT MAX(created_date) AS dt,user_id AS uid "
                            + "FROM betting GROUP BY user_id ORDER BY id DESC ) "
                            + "AS t1 ) "
                        + "AS t2";
            ResultSet rs = st.executeQuery(sql);

            while (rs.next())
            {
                mapData.put(rs.getInt("u"), rs.getString("dat"));
            }
            
            rs.close();
            st.close();
        } catch (Exception ex)
        {
          System.out.println(ex.getMessage());
        }
        
        return mapData;
    }

    public int getCurrentCoin(int userid,String date){
        int retVal = 0;
        try
        {
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery("SELECT current_coin FROM betting WHERE user_id = "+ userid +" AND DATE(FROM_UNIXTIME(created_date)) = '"+date+"' ORDER BY id ASC LIMIT 1");
            if (rs.next())
            {
               retVal = rs.getInt("current_coin");
            }
            rs.close();
            s.close();
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }

        return retVal;
    }

    public int getTotalPriceByDay(int user_id,String date)
    {
        int retVal = 0;

        try
        {
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery("SELECT SUM(price) as price FROM betting WHERE user_id = "+user_id+" AND DATE(FROM_UNIXTIME(created_date)) = '"+date+"' GROUP BY user_id");
            if (rs.next())
            {
               retVal = rs.getInt("price");
            }
            rs.close();
            s.close();
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }
        
        return retVal;
    }

    public int getTotalProfitByDay(int user_id,String date)
    {
        int retVal = 0;

        try
        {
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery("SELECT SUM(profit) as profit FROM betting WHERE user_id = "+user_id+" AND DATE(FROM_UNIXTIME(created_date)) = '"+date+"' and is_increase = 1 GROUP BY user_id");
            if (rs.next())
            {
               retVal = rs.getInt("profit");
            }
            rs.close();
            s.close();
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }

        return retVal;
    }

    public boolean updateCoinError(int userid,int coin)
    {
        boolean check = false;
        try {
            Statement s = conn.createStatement();
            String sql = "UPDATE user SET  coin = "+coin+" WHERE id = "+userid;
            s.execute(sql);
            check = true;
            s.close();

        } catch (Exception ex)
        {
          System.out.println(ex.getMessage());
        }
        return check;
    }

    public int getCoinExchange(int userid,String date)
    {
        int retVal = 0;
        
        try {
            Statement s = conn.createStatement();
            String sql = "SELECT exchange_number FROM coin_exchange WHERE user_id = "+userid+" and DATE(FROM_UNIXTIME(created_date)) = "+date+" order by user_id";
            ResultSet rs = s.executeQuery(sql);
            
            if (rs.next())
            {
               retVal = rs.getInt("exchange_number")*2;
            }
            
            s.close();

        } catch (Exception ex)
        {
          System.out.println(ex.getMessage());
        }
        return retVal;
    }

     public boolean updateTX(String team1,String team2,String tx_1,String tx_2,String tx_3,String hour_tx, String date_tx)
    {
         boolean check = false;
         String date = date_tx + "/" + String.valueOf(TimeUtil.getYear());
         try {
            Statement s = conn.createStatement();
            String sql = "UPDATE "+ TABLE_RATE + " SET  tx_1 = '" + tx_1 + "'," + " tx_2 = '" + tx_2 + "'," +" tx_rate = '" + tx_3 + "' WHERE team1 = '"+ team1 +"' AND team2 = '"+ team2 +"' AND hour = '"+ hour_tx +"' AND day = '"+ date +"'";
            Debug.println(sql);
            s.execute(sql);
            check = true;
            s.close();

        } catch (Exception ex)
        {
          System.out.println(ex.getMessage());
        }
        return check;

    }

}
